<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="text" version="1.0" encoding="UTF-8" indent="yes"/>
	<xsl:template match="/CogDataSet">
		<xsl:for-each select="TableInfo">
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[<xsl:value-of select="Schema"/>].[<xsl:value-of select="ManagedTable"/>_Insert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [<xsl:value-of select="Schema"/>].[<xsl:value-of select="ManagedTable"/>_Insert]
GO

CREATE PROCEDURE [<xsl:value-of select="Schema"/>].[<xsl:value-of select="ManagedTable"/>_Insert]
(
	<xsl:for-each select="ColumnInfo[IsPrimaryKey='false' and IsIdentity='false' and IsComputed='false']">@<xsl:value-of select="Column"/><xsl:text> </xsl:text><xsl:if test="DataType='XML'">VARCHAR(MAX)</xsl:if><xsl:if test="DataType!='XML'"><xsl:value-of select="DataType"/></xsl:if><xsl:if test="not(position()=last())"><xsl:if test="IsNullable = 'true'"> = NULL</xsl:if>,
	</xsl:if></xsl:for-each>
)
AS
BEGIN
INSERT INTO [<xsl:value-of select="Schema"/>].[<xsl:value-of select="Table"/>] (<xsl:for-each select="ColumnInfo[IsPrimaryKey='false' and IsIdentity='false' and IsComputed='false']">[<xsl:value-of select="Column"/>]<xsl:if test="not(position()=last())">, </xsl:if></xsl:for-each>)
VALUES (<xsl:for-each select="ColumnInfo[IsPrimaryKey='false' and IsIdentity='false' and IsComputed='false']"><xsl:if test="DataType='XML'">CAST(@<xsl:value-of select="Column"/> AS XML)</xsl:if><xsl:if test="DataType!='XML'">@<xsl:value-of select="Column"/></xsl:if><xsl:if test="not(position()=last())">, </xsl:if></xsl:for-each>)

SELECT <xsl:for-each select="ColumnInfo">[<xsl:value-of select="Column"/>]<xsl:if test="not(position()=last())">,
       </xsl:if></xsl:for-each>
FROM [<xsl:value-of select="Schema"/>].[<xsl:value-of select="ManagedTable"/>_Select]
WHERE <xsl:for-each select="ColumnInfo[IsPrimaryKey='true']">[<xsl:value-of select="Column"/>] = <xsl:choose><xsl:when test="IsIdentity='true'">SCOPE_IDENTITY()</xsl:when><xsl:otherwise>@<xsl:value-of select="Column"/></xsl:otherwise></xsl:choose><xsl:if test="not(position()=last())"> AND
</xsl:if>
</xsl:for-each>
END
GO
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>
